package service;

import Utils.DruidUtils;
import cn.hutool.core.date.DateTime;
import domain.News;
import domain.NewsType;
import domain.SysUser;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.util.ArrayList;
import java.util.List;

public class AdminService {

    public static Long id;

    public static List<News> getNewsData(Long pageSize, Long pageIndex) {
        List<News> newsDataList = null;

        // 操作数据库，在数据库中取值
        try {
            String sql = "select n.news_id as newsId, n.news_title as newsTitle, n.news_date as newsDate, n.news_image as newsImage, nt.news_type_name as newsTypeName from " +
                    "javawebsql.news as n join javawebsql.news_type as nt on n.news_type_id=nt.news_type_id limit ?, ?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            newsDataList = queryRunner.query(sql, new BeanListHandler<>(News.class), pageSize, pageIndex);

        } catch (Exception e) {
            System.out.println(e);
        }
        return newsDataList;
    }

    public static List<SysUser> getUserData(Long pageSize, Long pageIndex) {
        List<SysUser> userDataList = null;

        // 操作数据库，在数据库中取值
        try {
            String sql = "select user_name as userName, pwd, tel from test limit ?, ?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            userDataList = queryRunner.query(sql, new BeanListHandler<>(SysUser.class), pageSize, pageIndex);

        } catch (Exception e) {
            System.out.println(e);
        }
        return userDataList;
    }

    public static List<NewsType> getNewsType() {
        List<NewsType> userTypeList = null;

        // 操作数据库，在数据库中取值
        try {
            String sql = "select news_type_id as newsTypeId, news_type_name as newsTypeName from news_type";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            userTypeList = queryRunner.query(sql, new BeanListHandler<>(NewsType.class));

        } catch (Exception e) {
            System.out.println(e);
        }
        return userTypeList;
    }

    public static void insertData(Long typeId, String title, String content, String dateTime, String fileName) {

        // 操作数据库，在数据库中取值
        try {
            String sql = "insert into news(news_type_id, news_title, news_content, news_date, news_image) values (?, ?, ?, ?, ?)";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            queryRunner.execute(sql, new BeanListHandler<>(NewsType.class), typeId, title, content, dateTime, fileName);

        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static int getNewsTotal() {
        int count=0;
        List<News> nList;

        // 操作数据库，在数据库中取值
        try {
            String sql = "select n.news_id as newsId from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
             nList = queryRunner.query(sql, new BeanListHandler<>(News.class));

             count = nList.size();
        } catch (Exception e) {
            System.out.println(e);
        }
        return count;
    }

    public static int getUserTotal() {
        int total=0;
        List<SysUser> uList;

        // 操作数据库，在数据库中取值
        try {
            String sql = "select user_name as userName, pwd, tel from test";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            uList = queryRunner.query(sql, new BeanListHandler<>(SysUser.class));

            total = uList.size();
        } catch (Exception e) {
            System.out.println(e);
        }
        return total;
    }

    public static void updateData(Long typeId, String title, String content, String date, Long id) {
        News n = new News();

        // 操作数据库，在数据库中取值
        try {
            String sql = "update news set news_type_id=?, news_title=?,news_content=?,news_date=? where news_id=?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            queryRunner.execute(sql, new BeanHandler<>(News.class),typeId, title, content, date, id);

        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static List<News> getNewsContentByKeyWord(String keyWord){
        List<News> newsList = new ArrayList<>();
        // 操作数据库，在数据库中取值
        try {

            // 编写sql语句
            String sql = "select n.news_id newsId, n.news_title newsTitle, n.news_date newsDate, n.news_image newsImage, n.news_content newsContent, nt.news_type_name newsTypeName from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id where n.news_title like ?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            newsList = queryRunner.query(sql, new BeanListHandler<>(News.class), "%"+keyWord+"%");

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return newsList;
    }

    public static List<SysUser> getUserContentByKeyWord(String keyWord){
        List<SysUser> userList = new ArrayList<>();
        // 操作数据库，在数据库中取值
        try {

            // 编写sql语句
            String sql = "select user_name as userName, pwd, tel from test where user_name like ?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            userList = queryRunner.query(sql, new BeanListHandler<>(SysUser.class), "%"+keyWord+"%");

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return userList;
    }
}
